package db;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import util.ConsoleHelper;
import util.Constant;
import util.MyLogger;
import db.tables.DBActivityTable;
import db.tables.DBCategoryTable;
import db.tables.DBCountryTable;
import db.tables.DBGroupTable;
import db.tables.DBLanguageTable;
import db.tables.DBMessageTable;
import db.tables.DBRequestTable;
import db.tables.DBSchoolTable;
import db.tables.DBTownTable;
import db.tables.DBTypeTable;
import db.tables.DBUserNameTable;
import db.tables.DBUserSettingsTable;
import db.tables.DBUserTable;
import db.tables.DBWorkTable;
import db.tables.polyglott.DBCategoryPGTable;
import db.tables.reference.DBContactTable;
import db.tables.reference.DBGroupMemberTable;
import db.tables.reference.DBIsActiveTable;
import db.tables.reference.DBScholarAtTable;
import db.tables.reference.DBWorkAtTable;
import db.views.DBTownView;
import db.views.DBTranslationView;
/**
 * 
 * @author Kristin Behrens, Robin Gawenda, Swen Jung
 * @version 1.0
 * 	
 */
public class DBMain extends MyLogger{

	/**
	 * Represents the user data table. Needs to be first instantiated, as other
	 * tables rely on this.
	 */
	
	//protected static DBSettingsTable settingsTable =null; VERSION MANAGEMENT FOR DB REQUIRED!!!!
	
	protected static DBCountryTable countryTable = null;
	protected static DBTownTable townTable = null;
	protected static DBLanguageTable languageTable=null;

	protected static DBTypeTable typeTable = null;
	protected static DBCategoryTable categoryTable = null;
	protected static DBCategoryPGTable categoryPGTable=null; //PG => polyglott 

	protected static DBUserTable userTable = null;
	protected static DBUserNameTable userNameTable = null;
	protected static DBUserSettingsTable userSettingsTable = null;
	
	protected static DBGroupTable groupTable = null;
	protected static DBGroupMemberTable groupMemberTable = null;

	protected static DBRequestTable requestTable = null;
	protected static DBMessageTable messageTable = null;

	protected static DBContactTable contactTable = null;

	protected static DBWorkTable workTable = null;
	protected static DBWorkAtTable workAtTable = null;
	protected static DBSchoolTable schoolTable = null;
	protected static DBScholarAtTable scholarAtTable = null;

	protected static DBActivityTable activityTable = null;
	protected static DBIsActiveTable isActiveTable = null;
	
	protected static DBTownView townView = null;
	protected static DBTranslationView translationView=null;

	protected static ArrayList<DBObject> tables = null;
	
	/**
	 * The basic String for the compare method
	 */
	private static final String protoTime = "SELECT usr.\'temp1\',usr.\'temp2\',usr.user_id,usr.start_date,usr.end_date "
		+ "FROM \'table1\' AS my_usr JOIN \'table1\' AS usr "
		+ "ON (my_usr.\'temp1\'=usr.\'temp1\' "
		+ "AND ((usr.start_date>=my_usr.start_date AND usr.end_date<=my_usr.end_date)"
		+ "OR(usr.start_date<=my_usr.start_date AND usr.end_date >=my_usr.end_date)"
		+ "OR(usr.start_date<my_usr.start_date AND usr.end_date<my_usr.end_date AND usr.end_date>my_usr.start_date)"
		+ "OR(usr.start_date>my_usr.start_date AND usr.end_date>my_usr.end_date AND usr.start_date<my_usr.end_date)))";
	private static final String protoUnknown = "SELECT * FROM \'table1\' WHERE "
		+ "\'temp2\' IN (SELECT \'temp2\' FROM \'table1\' WHERE user_id=?) "
		+ "AND user_id<>? "
		+ "AND user_id NOT IN (SELECT contact_user_id FROM contact WHERE user_id=?)";
	private static final String protoFinalState="SELECT * FROM ("+protoTime+") UNION ("+protoUnknown+");";

	protected static void initTables() {
		MyLogger.loadLoggerSettings();
		try {
			if (tables == null){
				tables = new ArrayList<DBObject>();
				DBConnection.checkUsers();
			}

			if (countryTable == null)
				tables.add(countryTable = new DBCountryTable());
			if (townTable == null)
				tables.add(townTable = new DBTownTable());
			if (townView==null)
				townView=new DBTownView();
			
			if (languageTable==null)
				tables.add(languageTable = new DBLanguageTable());

			if (typeTable == null)
				tables.add(typeTable = new DBTypeTable());
			if (categoryTable == null)
				tables.add(categoryTable = new DBCategoryTable());
			if (categoryPGTable == null)
				tables.add(categoryPGTable = new DBCategoryPGTable());
			if(translationView==null)
				translationView=new DBTranslationView();
			
			if (userTable == null)
				tables.add(userTable = new DBUserTable());
			if (userNameTable==null)
				tables.add(userNameTable = new DBUserNameTable());
			if (userSettingsTable == null)
				tables.add(userSettingsTable = new DBUserSettingsTable());

			if (groupTable == null)
				tables.add(groupTable = new DBGroupTable());
			if (groupMemberTable == null)
				tables.add(groupMemberTable = new DBGroupMemberTable());

			if (requestTable == null)
				tables.add(requestTable = new DBRequestTable());
			if (messageTable == null)
				tables.add(messageTable = new DBMessageTable());

			if (contactTable == null)
				tables.add(contactTable = new DBContactTable());

			if (workTable == null)
				tables.add(workTable = new DBWorkTable());
			if (workAtTable == null)
				tables.add(workAtTable = new DBWorkAtTable());

			if (schoolTable == null)
				tables.add(schoolTable = new DBSchoolTable());
			if (scholarAtTable == null)
				tables.add(scholarAtTable = new DBScholarAtTable());

			if (activityTable == null)
				tables.add(activityTable = new DBActivityTable());
			if (isActiveTable == null)
				tables.add(isActiveTable = new DBIsActiveTable());
			
			insertTableInfos();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		new ConsoleHelper();
	}
	
	private static boolean insertTableInfos(){
		int i=0;
		String[] col ={DBCategoryTable.TYPE_ID_COL,DBCategoryTable.CATEGORY_NAME_COL,DBCategoryTable.CATEGORY_ORDER_COL};
		Long pk=getTypeTable().getTypeID(Constant.Type.TABLE_DATA);
		if(pk==null)pk=getTypeTable().insertGetPK(new String[]{DBTypeTable.TYPE_NAME_COL}, new String[]{Constant.Type.TABLE_DATA});
		for(DBObject table:tables){
			if(!getCategoryTable().existsValue(DBCategoryTable.CATEGORY_NAME_COL, table.tableName)){
				getCategoryTable().insert(col, 
						new Object[]{pk,table.tableName,i});
				for(Object[] columns:table.default_columns){
					getCategoryTable().insert(col, 
							new Object[]{pk,columns[DBObject.DEFAULT_COLUMN_NAME],i});
				}
			}
			i++;	
		}
		
		String path="/config/data.props";
		File f=new File(path);
		if(!f.exists())f= new File("./WebContent/data/data.props");
		ArrayList<String[]> translationData=parseData(f.toString());
		if(translationData!=null)insertTranslations(translationData);
		return false;
	}

	private static boolean insertTranslations(ArrayList<String[]> translationData){
		String[] cols={DBCategoryPGTable.CATEGORY_ID_COL,
				DBCategoryPGTable.LANGUAGE_ID_COL,
				DBCategoryPGTable.CATEGORY_NAME_PG_COL};
		int cat_id=0,lang=5,name_pg=4;
		Long lang_id=null;
		for(int j=0;j<translationData.get(0).length;j++){
			String s=translationData.get(0)[j];
			if(s.equalsIgnoreCase(DBCategoryPGTable.CATEGORY_ID_COL))
				cat_id=j;
			else if(s.equalsIgnoreCase(DBLanguageTable.LANGUAGE_SHORT_COL))
				lang=j;
			else if(s.equalsIgnoreCase(DBCategoryPGTable.CATEGORY_NAME_PG_COL))
				name_pg=j;
		}
		translationData.remove(0);
		
		for(String[] data:translationData){
			lang_id=languageTable.selectLong(DBLanguageTable.LANGUAGE_ID_COL, DBLanguageTable.LANGUAGE_SHORT_COL, data[lang]);
			if(!(categoryPGTable.existsValue(DBLanguageTable.LANGUAGE_ID_COL, lang_id)
					&&categoryPGTable.existsValue(DBCategoryPGTable.CATEGORY_ID_COL, data[cat_id]))){
				categoryPGTable.insert(cols, new Object[]{data[cat_id],lang_id,data[name_pg]});
			}
		}
		return false;
	}
	
	public static ArrayList<String[]> parseData(String file){
		String line=null, regex=";";
		ArrayList<String[]> data=null;
		File f=new File(file);

		if(f.exists()){
			try {
				data=new ArrayList<String[]>();
				BufferedReader lis=new BufferedReader(new FileReader(f));
				while((line=lis.readLine())!=null){
					data.add(line.split(regex));
				}
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}
			
		}else log.warning("The file does not exist: "+file);
		return data;
	}

	public static void dropTables() {
		for (DBObject obj : tables) {
			obj.dropTable();
		}
	}

	public static DBUserTable getUserTable() {
		if (userTable == null) {
			initTables();
		}
		return userTable;
	}

	public static DBUserNameTable getUserNameTable() {
		if (userNameTable == null)
			initTables();
		return userNameTable;
	}

	public static DBTypeTable getTypeTable() {
		if (typeTable == null)
			initTables();
		return typeTable;
	}

	public static DBCategoryTable getCategoryTable() {
		if (categoryTable == null)
			initTables();
		return categoryTable;
	}

	public static DBCountryTable getCountryTable() {
		if (countryTable == null)
			initTables();
		return countryTable;
	}

	public static DBTownTable getTownTable() {
		if (townTable == null)
			initTables();
		return townTable;
	}

	public static DBGroupTable getGroupTable() {
		if (groupTable == null)
			initTables();
		return groupTable;
	}

	public static DBGroupMemberTable getGroupMemberTable() {
		if (groupMemberTable == null)
			initTables();
		return groupMemberTable;
	}

	public static DBRequestTable getRequestTable() {
		if (requestTable == null)
			initTables();
		return requestTable;
	}

	public static DBMessageTable getMessageTable() {
		if (messageTable == null)
			initTables();
		return messageTable;
	}

	public static DBContactTable getContactTable() {
		if (contactTable == null)
			initTables();
		return contactTable;
	}

	public static DBWorkTable getWorkTable() {
		if (workTable == null)
			initTables();
		return workTable;
	}

	public static DBWorkAtTable getWorkAtTable() {
		if (workAtTable == null)
			initTables();
		return workAtTable;
	}

	public static DBSchoolTable getSchoolTable() {
		if (schoolTable == null)
			initTables();
		return schoolTable;
	}

	public static DBScholarAtTable getScholarAtTable() {
		if (scholarAtTable == null)
			initTables();
		return scholarAtTable;
	}

	public static DBActivityTable getActivityTable() {
		if (activityTable == null)
			initTables();
		return activityTable;
	}

	public static DBIsActiveTable getIsActiveTable() {
		if (isActiveTable == null)
			initTables();
		return isActiveTable;
	}

	public static ArrayList<DBObject> getTables() {
		if (tables == null)
			initTables();
		return tables;
	}

	public static ArrayList<ArrayList<Object>> activityCompare(Long user_id){
		return basicCompareFunction(DBIsActiveTable.IS_ACTIVE_ID_COL,DBIsActiveTable.ACTIVITY_ID_COL,DBIsActiveTable.TABLE_NAME,user_id);
	}

	public static ArrayList<ArrayList<Object>> workCompare(Long user_id){
		return basicCompareFunction(DBWorkAtTable.WORK_AT_ID_COL,DBWorkAtTable.WORK_ID_COL,DBWorkAtTable.TABLE_NAME,user_id);
	}

	public static ArrayList<ArrayList<Object>> groupCompare(Long user_id){
		return basicCompareFunction(DBGroupMemberTable.GROUP_MEMBER_ID_COL,DBGroupMemberTable.GROUP_ID_COL,DBGroupMemberTable.TABLE_NAME,user_id);
	}

	public static ArrayList<ArrayList<Object>> schoolCompare(Long user_id){
		return basicCompareFunction(DBScholarAtTable.SCHOLAR_AT_ID_COL,DBScholarAtTable.SCHOOL_ID_COL,DBScholarAtTable.TABLE_NAME,user_id);
	}

	protected static ArrayList<ArrayList<Object>> basicCompareFunction(String col1, String col2, String table, Long user_id) {
		ArrayList<ArrayList<Object>> result=null;
		ResultSet rs=null;
		int max=3;
		try {
			String stmt=protoFinalState.replaceAll("\'temp1\'", col1);
			stmt=stmt.replaceAll("\'temp2\'", col2);
			stmt=stmt.replaceAll("\'table1\'", table);
			PreparedStatement ps= DBConnection.getConnection().prepareStatement(stmt);
			ps.clearParameters();
			for(int i=1;i<=max;i++)
				ps.setLong(i, user_id);
			rs = ps.executeQuery();
			result=DBSQLUtil.getResultSetAsArrayList(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}finally{
			DBObject.closeResult(rs);
		}
		return result;
	}

	public static DBLanguageTable getLanguageTable() {
		if(languageTable==null){
			initTables();
		}
		return languageTable;
	}

	public static DBCategoryPGTable getCategoryPGTable() {
		if(categoryPGTable==null){
			initTables();
		}
		return categoryPGTable;
	}

	public static DBUserSettingsTable getUserSettingsTable() {
		if(userSettingsTable==null){
			initTables();
		}
		return userSettingsTable;
	}

	public static DBTownView getTownView() {
		if(townView==null){
			initTables();
		}
		return townView;
	}

	public static DBTranslationView getTranslationView() {
		if(translationView==null){
			initTables();
		}
		return translationView;
	}
}